/*=======================================================================
Creator: Jingyuan Wang, jingyuanwang@u.northwestern.edu
Date created: 				03/17/2019
Date last modified: 		
Purpose: 
		Use Misato energy price and trade data to generate international energy price
		for 100 NAICS6 industries. 
		The final dataset will be naics6-by-year
		
		Steps:
			I Import foreign energy price indices from Misato
			II Weight it by use the import and export data by country at NAICS6 that Karl already created
				1. merge in the import and export data
				2. weight
			III. Save
==========================================================================*/

global version "_region-level"

*************************************************************************
* 				PART I. Import the import and export data				*
*************************************************************************

use "$Schott/us_imports_naics-cntry-89-17.dta", clear
merge 1:1 naics_str year isocode_uniform region region_wb using "$Schott/us_exports_naics-cntry-89-17.dta"
drop _merge
drop if year > 2015

replace imports = 0 if imports == .
replace exports = 0 if exports == .

*************************************************************************
* 				PART II. merge with misato								*
*************************************************************************

* 1. merge-in sector code (consistent with misato)
gen naics_5digit = substr(naics_str, 1,5)
destring naics_5digit, replace

* change some 2012 code to 2007 version
replace naics_5digit = 31133 if naics_5digit == 31135
replace naics_5digit = 31221 if naics_5digit == 31223
replace naics_5digit = 31523 if naics_5digit == 31524
replace naics_5digit = 31529 if naics_5digit == 31528
replace naics_5digit = 33329 if naics_5digit == 33324


merge m:1 naics_5digit using  "$crosswalks/crosswalk_censusnaics_iea_allindustries.dta"
drop if _merge == 2
drop _merge
order naics_5digit naics_3digit sector, a(naics_str)

* is this line on textiles doing anything???
replace sector = "Textile and leather" if sector == "Textile and leather" 
replace sector = "Manufacturing"  if sector == "Non-specified (Industry)"

* 2. merge with misato
rename isocode_uniform isoalpha3code
keep if year >= 1995 & year <= 2015
merge m:1 isoalpha3code year sector using "$misato/VEPLdata_final_update2017.dta"
drop if _merge == 2
order country, a(isoalpha3code)
drop _merge


	
*************************************************************************
* 				PART III. merge-in regional price index					*
*************************************************************************

* 1. separate all the countries into 7 regions
replace region = "Europe" if isoalpha3code == "AUT"
tab region_wb region if region_wb == "Europe & Central Asia"
replace region_wb = "Central Asia" if region_wb == "Europe & Central Asia" & region == "Asia"
replace region_wb = "Europe" if region_wb == "Europe & Central Asia" & region == "Europe"

rename isoalpha3code iso
drop region
gen region = region_wb
replace region = "Central America" if region_wb == "North America"
replace region = "CAN" if iso == "CAN"
replace region = "Asia & Pacific" if region_wb == "East Asia & Pacific" | region_wb == "Central Asia" | region_wb == "South Asia"
replace region = "South America" if region_wb == "Latin America & Caribbean"
replace region = "Africa" if region_wb == "Sub-Saharan Africa"

merge m:1 sector region year using "$energy_price/importspriceindex.dta"
drop _merge
rename price_index priceindex_imports

merge m:1 sector region year using "$energy_price/exportspriceindex.dta"
drop _merge
rename price_index priceindex_exports




*************************************************************************
* 				PART III. generate import/export price					*
*************************************************************************
	
* generate a variable to indicate to what % we are missing prices
* (1) imports
gen flag_price = 1
replace flag_price = 0 if priceindex_imports == . & FEPI_fw2010 == .
* generate total imports per industry per year
bysort naics_str year: egen imports_total = total(imports)
* imports from countries with price available per industry per year
gen nonmissing = flag_price * imports
bysort naics_str year: egen imports_nonmissing = total(nonmissing)
drop nonmissing
* the rate of nonmissing imports over total imports per year per industry
gen imports_nonmissing_rate = imports_nonmissing / imports_total
replace imports_nonmissing_rate = 1 if imports_total == 0
* the share of each country to the total imports
gen importshare_percountry = imports / imports_total
drop flag_price

* (2) exports
gen flag_price = 1
replace flag_price = 0 if priceindex_exports == . & FEPI_fw2010 == .
* generate total imports per industry per year
bysort naics_str year: egen exports_total = total(exports)
* imports from countries with price available per industry per year
gen nonmissing = flag_price * exports
bysort naics_str year: egen exports_nonmissing = total(nonmissing)
drop nonmissing
* the rate of nonmissing imports over total imports per year per industry
gen exports_nonmissing_rate = exports_nonmissing / exports_total
replace exports_nonmissing_rate = 1 if exports_total == 0
* the share of each country to the total imports
gen exportshare_percountry = exports / exports_total
drop flag_price

order imports_total imports_nonmissing imports_nonmissing_rate importshare_percountry ///
		exports_total exports_nonmissing exports_nonmissing_rate exportshare_percountry, b(exports)




*************************************************************************
* 				PART VI. generate import/export price					*
*************************************************************************

* 0. correct the price
replace FEPI_fw2010 = exp(FEPI_fw2010)
gen importsprice = FEPI_fw2010
replace importsprice = priceindex_imports if FEPI_fw2010 == .
gen exportsprice = FEPI_fw2010
replace exportsprice = priceindex_exports if FEPI_fw2010 == .

* 1. import
gen totalprice = importsprice * imports
bysort naics_str year: egen imports_totalprice = total(totalprice)
drop totalprice
gen price_imports = imports_totalprice / imports_nonmissing

* 2. export
gen totalprice = exportsprice * exports
bysort naics_str year: egen exports_totalprice = total(totalprice)
drop totalprice
gen price_exports = exports_totalprice / exports_nonmissing



*************************************************************************
* 				PART V. save											*
*************************************************************************

sort naics_str year iso
keep naics_str naics_* year imports_nonmissing_rate exports_nonmissing_rate imports_total exports_total price_import price_export
duplicates drop

label var price_imports "2010$ per TOE"
label var price_exports "2010$ per TOE"
label var imports_total "billion USD"
label var exports_total "billion USD"

replace price_imports = price_imports / 39.68
replace price_exports = price_exports / 39.68
label var price_imports "2010$ per million Btu"
label var price_exports "2010$ per million Btu"

* save
local version "$version"
save "$energy_price/importexport_energy_price_industry_year`version'.dta", replace

